﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Types;
using GeoVision.Addin.Helpers;
using GeoVision.Addin.Core;
using GeoVision.Addin.Core.DataManagement;

namespace GeoVision.Addin.UI.SQLSpatial
{
    public enum WhichDatabase
    {
        CUSTOMDATABASE, MASTER
    }
    public enum SQLGeoType
    {
        GEOMETRY,
        GEOGRAPHY
    }
   
    public partial class SQLForm : Form
    {
        public string ConnectionString { get; set; }
        public string TableName { get; set; }
        public string GeoFieldName { get; set; }
        public double Reduce { get; set; }

        public ViewSchema Schema { get; set; }

        private static List<string> Databases = null;
        private static bool SqlAuthentication = false;
        private static string lastUser = "";
        private static string lastTable = "";
        private static string lastDatabase = "";
        private static double lastReduce = 0;
        private static string lastInstance = "";
        private static string lastPassword = "";

        List<GeoField> geoList;
        public SQLForm()
        {
            LocalizationHelper.SetCulture();
            InitializeComponent();
        }
        private void ctlSqlServerInstances_SelectedIndexChanged(object sender, EventArgs e)
        {
            ctlSqlServerDatabases.Enabled = true;

        }

        #region PrivateMethods
        static List<string> GetSqlServerInstancesList()
        {
            List<string> _instances = new List<string>();
            SQLInfoEnumerator sie = new SQLInfoEnumerator();
            _instances.AddRange(sie.EnumerateSQLServers());
            return _instances;
        }

        string GetConnectionString(WhichDatabase whichDatabase)
        {
            string _connectionStr = string.Empty;
            string DataSource = ctlSqlServerInstances.Text;
            string DatabaseName = ctlSqlServerDatabases.SelectedItem != null ? ctlSqlServerDatabases.SelectedItem.ToString() : string.Empty;
            if (ctlIntegraedSecurity.SelectedItem.ToString() == "FALSE")
            {
                string UserName = ctlUsername.Text;
                string Password = ctlPassword.Text;
                _connectionStr = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};", DataSource, whichDatabase == WhichDatabase.MASTER ? "Master" : DatabaseName, UserName, Password);
            }
            else
            {
                _connectionStr = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", DataSource, whichDatabase == WhichDatabase.MASTER ? "Master" : DatabaseName);
            }
            return _connectionStr;
        }
        #endregion

        static SQLForm()
        {
            try
            {
                Databases = GetSqlServerInstancesList();
            }
            catch
            {
            }
        }

        private void ctlIntegraedSecurity_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ctlIntegraedSecurity.SelectedItem.ToString() == "FALSE")
                gbAccountInfo.Enabled = true;
            else if (ctlIntegraedSecurity.SelectedItem.ToString() == "TRUE")
                gbAccountInfo.Enabled = false;
        }

        private void DbMenuForm_Load(object sender, EventArgs e)
        {
            ctlSqlServerInstances.DataSource = Databases;
            if (Databases.IndexOf(lastInstance) >= 0)
                ctlSqlServerInstances.SelectedIndex = Databases.IndexOf(lastInstance);
            if (SqlAuthentication)
            {
                ctlIntegraedSecurity.SelectedIndex = 1;
                ctlIntegraedSecurity_SelectedIndexChanged(this, EventArgs.Empty);
            }
            else ctlIntegraedSecurity.SelectedIndex = 0;
            ctlUsername.Text = lastUser;
            ctlReduceTolerance.Value = Convert.ToDecimal(lastReduce);
            ctlPassword.Text = lastPassword;
            if (!string.IsNullOrEmpty(lastPassword))
                ctlSavePwd.Checked = true;
        }

        private void ctlConnectBtn_Click(object sender, EventArgs e)
        {
            ctlSqlServerDatabases.Items.Clear();
            using (SqlConnection con = new SqlConnection(GetConnectionString(WhichDatabase.MASTER)))
            {
                con.Open();
                if (con.State == ConnectionState.Closed)
                    MessageBox.Show("Connection Failed");
                else
                {
                    SqlCommand cmd = new SqlCommand(SQLQueryText.SelectAllUserDatabasesQuery, con);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        ctlSqlServerDatabases.Items.Add(reader[0]);
                    }
                    reader.Close();
                }
            }
            gbAdvacedOptions.Enabled = true;
            if (ctlSqlServerDatabases.Items.IndexOf(lastDatabase) >= 0)
                ctlSqlServerDatabases.SelectedItem = lastDatabase;
        }

        private DataTable GetSpecificList(SqlConnection xcon, string xcommand, SqlParameter xparams)
        {

            SqlCommand xcmd = new SqlCommand();
            xcmd.Connection = xcon;
            xcmd.CommandType = CommandType.StoredProcedure;
            xcmd.CommandText = xcommand;
            xcmd.Parameters.AddWithValue(xparams.ParameterName, xparams.Value);
            SqlDataAdapter xda = new SqlDataAdapter(xcmd);
            DataTable xdt = new DataTable();
            xda.Fill(xdt);
            xcon.Close();
            return xdt;
        }

        private void ctlSqlServerDatabases_SelectedIndexChanged(object sender, EventArgs e)
        {
            ctlSqlServerTables.Enabled = true;
            ctlSqlServerTables.Items.Clear();
            using (SqlConnection con = new SqlConnection(GetConnectionString(WhichDatabase.CUSTOMDATABASE)))
            {
                con.Open();
                if (con.State == ConnectionState.Closed)
                    MessageBox.Show("Connection Failed");
                else
                {
                    SqlCommand cmd = new SqlCommand(SQLQueryText.SelectTableNamesQuery, con);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {

                        ctlSqlServerTables.Items.Add(reader[0]);
                    }
                    reader.Close();
                }
            }
            if (ctlSqlServerTables.Items.IndexOf(lastTable) >= 0)
            {
                ctlSqlServerTables.SelectedItem = lastTable;
                ctlSqlServerTables_SelectedIndexChanged(this, EventArgs.Empty);
            }
        }

        public DialogResult Execute()
        {

            return ShowDialog();
        }

        private void ctlStartDrawingButton_Click(object sender, EventArgs e)
        {
            if (ctlSqlServerTables.SelectedItem != null && ctlGeoFields.SelectedItem != null)
            {
                GeoField field = ctlGeoFields.SelectedItem as GeoField;
                this.Reduce = Convert.ToDouble(ctlReduceTolerance.Value);
                this.TableName = ctlSqlServerTables.SelectedItem.ToString();
                this.GeoFieldName = field.FieldName;
                this.ConnectionString = GetConnectionString(WhichDatabase.CUSTOMDATABASE);

                if (Databases.IndexOf(ctlSqlServerInstances.Text) < 0)
                    Databases.Add(ctlSqlServerInstances.Text);
                lastTable = this.TableName;
                lastUser = ctlUsername.Text;
                lastDatabase = ctlSqlServerDatabases.SelectedItem.ToString();
                SqlAuthentication = ctlIntegraedSecurity.SelectedIndex == 1;
                if (SqlAuthentication && ctlSavePwd.Checked)
                    lastPassword = ctlPassword.Text;
                else lastPassword = "";
                lastReduce = this.Reduce;
                lastInstance = ctlSqlServerInstances.Text;
            }
            this.Close();
        }

        private DataType GetDataType(string sqldbtype)
        {
            Dictionary<string, DataType> xtypes = new Dictionary<string, DataType>();
            xtypes.Add("bit", DataType.Bool);
            xtypes.Add("date", DataType.DateTime);
            xtypes.Add("datetime", DataType.DateTime);
            xtypes.Add("datetime2", DataType.DateTime);
            xtypes.Add("decimal", DataType.Decimal);
            xtypes.Add("float", DataType.Double);
            xtypes.Add("int", DataType.Integer);
            xtypes.Add("money", DataType.Decimal);
            xtypes.Add("nchar", DataType.String);
            xtypes.Add("ntext", DataType.String);
            xtypes.Add("nvarchar", DataType.String);
            xtypes.Add("real", DataType.Double);
            xtypes.Add("smallint", DataType.Integer);
            xtypes.Add("smallmoney", DataType.Decimal);
            xtypes.Add("sql_variant", DataType.Unknown);
            xtypes.Add("text", DataType.String);
            xtypes.Add("bigint", DataType.Int64);
            xtypes.Add("time", DataType.DateTime);
            xtypes.Add("tinyint", DataType.Integer);
            xtypes.Add("smalldatetime", DataType.DateTime);
            xtypes.Add("varchar", DataType.String);
            xtypes.Add("int identity", DataType.Integer);
            xtypes.Add("image", DataType.Blob);
            xtypes.Add("varbinary", DataType.Blob);
            xtypes.Add("binary", DataType.Blob);
            xtypes.Add("uniqueidentifier", DataType.String);
            xtypes.Add("xml", DataType.Unknown);
            xtypes.Add("timestamp", DataType.DateTime);
            xtypes.Add("geography", DataType.Spatial);
            xtypes.Add("geometry", DataType.Spatial);

            if (!xtypes.ContainsKey(sqldbtype))
                return DataType.Unknown;
            else return xtypes[sqldbtype];
        }

        private void ctlSqlServerTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            geoList = new List<GeoField>();
            string tableName = ctlSqlServerTables.SelectedItem.ToString();
            using (SqlConnection con = new SqlConnection(GetConnectionString(WhichDatabase.CUSTOMDATABASE)))
            {
                con.Open();
                if (con.State == ConnectionState.Closed)
                    MessageBox.Show("Connection Failed");
                else
                {
                    string cmdText = string.Format(SQLQueryText.SelectGeometryQuery, tableName);
                    string cmdTextGeography = string.Format(SQLQueryText.SelectGeographyQuery, tableName);
                    using (SqlCommand cmd = new SqlCommand(cmdText, con))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                geoList.Add(new GeoField { FieldName = reader[0].ToString(), GeoType = SQLGeoType.GEOMETRY });
                            }
                        }
                    }
                    using (SqlCommand cmd = new SqlCommand(cmdTextGeography, con))
                    {
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                geoList.Add(new GeoField { FieldName = reader[0].ToString(), GeoType = SQLGeoType.GEOGRAPHY });
                            }
                        }
                    }
                    SqlParameter xcolumnlistparams = new SqlParameter("@table_name", tableName);
                    DataTable xcolumnlist = GetSpecificList(con, "sp_columns", xcolumnlistparams);
                    Schema = new ViewSchema();
                    foreach (DataRow xcolumnitem in xcolumnlist.Rows)
                    {
                        Schema.Columns.Add(new Column() { Name = xcolumnitem["COLUMN_NAME"].ToString(), Type = GetDataType(xcolumnitem["TYPE_NAME"].ToString()) });
                    }
                }
            }
            ctlGeoFields.DataSource = geoList;
            ctlGeoFields.DisplayMember = "FieldName";
            geoList.Clear();
        }

        private void ctlGeoFields_SelectedIndexChanged(object sender, EventArgs e)
        {
            ctlStartDrawingButton.Enabled = true;
        }

        private void groupBox2_Enter(object sender, EventArgs e)
        {

        }
    }

    public class GeoField
    {
        public string FieldName { get; set; }
        public SQLGeoType GeoType { get; set; }
    }
}
